﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Collections;

namespace LUStationery.DAO
{
    public class RequestDAO
    {
        SqlConnection cn;
        SqlCommand cmd;
        public RequestDAO() 
        {
            cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
        }

        public IDataReader getRequestsByUserName(string username)
        {
            cmd = new SqlCommand("select * from Request where user_name=@username", cn);
            cmd.Parameters.Add("@username", SqlDbType.VarChar).Value = username;
            cn.Open();
            IDataReader idr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            return idr;
        }
        public IDataReader getRequestsByStatus(string status)
        {
            cmd = new SqlCommand("select * from Request where request_status=@status", cn);
            cmd.Parameters.Add("@status", SqlDbType.VarChar).Value = status;
            cn.Open();
            IDataReader idr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            return idr;
        }
        public IDataReader geRequestsByStaffIDAndStatus(string staffID, string status)
        {
            cmd = new SqlCommand("select * from Request where staff_id=@staff_id and request_status=@status", cn);
            cmd.Parameters.Add("@staff_id", SqlDbType.VarChar).Value = staffID;
            cmd.Parameters.Add("@status", SqlDbType.VarChar).Value = status;
            cn.Open();
            IDataReader idr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            return idr;
        }

        public IDataReader geRequestsByStaffIDAndStatusAndRequisitionFormID(string staffID, string status,string requisitionFormID)
        {
            cmd = new SqlCommand("select * from Request where staff_id=@staff_id and request_status=@status and requisition_form_id=@requisitionFormID", cn);
            cmd.Parameters.Add("@staff_id", SqlDbType.VarChar).Value = staffID;
            cmd.Parameters.Add("@status", SqlDbType.VarChar).Value = status;
            cmd.Parameters.Add("@requisitionFormID", SqlDbType.VarChar).Value = requisitionFormID;
            cn.Open();
            IDataReader idr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            return idr;
        }

        public IDataReader getRequisitionFormIDByUserName(string username)
        {
            cmd = new SqlCommand("select requisition_form_id from Request where UserName=@username group by requisition_form_id", cn);
            cmd.Parameters.Add("@username", SqlDbType.VarChar).Value = username;
            cn.Open();
            IDataReader idr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            return idr;
        }

    }
}
